package com.ruoyi.standingbook.recycle.service.impl;

import com.ruoyi.common.core.service.OssService;
import com.ruoyi.common.domain.vo.RecycleErecordVo;
import com.ruoyi.common.domain.vo.StatisticsRecycleVo;
import com.ruoyi.common.tools.constant.ERecordConstant;
import com.ruoyi.common.tools.excel.MyExcelUtil;
import com.ruoyi.common.tools.excel.base.MyExcelResult;
import com.ruoyi.common.tools.excel.listener.RecycleExcelListener;
import com.ruoyi.common.tools.utils.SerialGenerator;
import com.ruoyi.standingbook.recycle.domain.bo.RecycleBo;
import com.ruoyi.standingbook.recycle.domain.bo.RecycleImportBaseBo;
import com.ruoyi.standingbook.recycle.domain.vo.RecycleImportDetailVo;
import com.ruoyi.standingbook.recycle.domain.vo.RecycleVo;
import com.ruoyi.standingbook.recycle.service.IRecycleImportBaseService;
import com.ruoyi.standingbook.recycle.service.IRecycleImportDetailService;
import com.ruoyi.standingbook.recycle.service.IRecycleService;
import com.ruoyi.system.service.ISysUserService;
import lombok.RequiredArgsConstructor;
import org.apache.http.client.utils.DateUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RequiredArgsConstructor
@Service
public class RecycleExcelService {
    private final IRecycleService RecycleService;
    private final IRecycleImportBaseService RecycleImportBaseService;
    private final IRecycleImportDetailService RecycleImportDetailService;
    private final OssService ossService;

    /**
     * 导入excel数据
     *
     * @param bo
     * @return
     */
    public Boolean importExcel(RecycleImportBaseBo bo) {
        // 生成批次号
        final String batchNo = SerialGenerator.generator.nextId();
        bo.setBatchNo(batchNo);

        // excel 处理
        MyExcelResult<RecycleImportDetailVo> excelResult = MyExcelUtil.getImportExcelData(batchNo, bo.getExcelUrl(),
            new RecycleExcelListener(batchNo), RecycleImportDetailVo.class, true);

        if (null == excelResult) {
            return false;
        }

        // 更新主表状态
        if (excelResult.isSuccess()) {
            bo.setProcessStatus(ERecordConstant.COMMON_STATUS_SUCCESS);
        } else {
            bo.setProcessStatus(ERecordConstant.COMMON_STATUS_ERROR);
        }
        bo.setProcessMsg(excelResult.getAnalysis());
        bo.setAttachMsg(ERecordConstant.ATTACH_UPLOAD_STATUS_NO);
        // 入库主表
        if (this.RecycleImportBaseService.insertByBo(bo)) {
            // 入库子表
            if (!excelResult.getList().isEmpty()) {
                if (!this.RecycleImportDetailService.batchInsertVo(excelResult.getList())) {
                    return false;
                }
            }
        } else {
            return false;
        }

        if (excelResult.isSuccess() && !excelResult.getList().isEmpty()) {
            this.RecycleService.batchInsert(excelResult.getList());
        }
        return true;
    }

    /**
     * 导出垃圾清运日报
     */
    public void exportDailyReport(RecycleBo bo, HttpServletResponse response) {
        String[] garbageCategorys = {"1", "2", "3", "4"};
        String[] garbageCategoryNames = {"厨余垃圾", "废旧家具", "生活垃圾", "果蔬垃圾"};
        try {
            OutputStream output = response.getOutputStream();
            // 产生工作薄对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            //查询当月垃圾清运汇总数据
            bo.setDate(DateUtils.formatDate(new Date(), "yyyy-MM"));
            RecycleErecordVo rev = RecycleService.getRecycleErecordInfo(bo);
            for (int c = 0; c < garbageCategorys.length; c++) {
                bo.setGarbageCategory(garbageCategorys[c]);
                List<RecycleVo> rvList = RecycleService.queryExcelList(bo);
                // 产生工作表对象
                HSSFSheet sheet = workbook.createSheet();
                // 设置工作表的名称.
                workbook.setSheetName(c, garbageCategoryNames[c]);
                HSSFRow row;
                HSSFCell cell;// 产生单元格
                int rowIndex = 0;
                row = sheet.createRow(rowIndex);// 产生一行
                row.setHeight((short) 700);
                /* *********普通列样式********* */
                HSSFFont font = workbook.createFont();
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                font.setFontName("Arail narrow"); // 字体
                //标题列
                String[] titles = {"", "", "", "   ", "序号", "日期", "司机姓名", "车牌", "清运路线", "垃圾清运要求", "桶数（L）", "重量（KG）", "垃圾桶状况", "处理站"};
                int[] widths = {625 * 6, 625 * 13, 625 * 13, 625 * 6, 625 * 6, 625 * 6, 625 * 6, 625 * 6, 625 * 12, 625 * 12, 625 * 6, 625 * 6, 625 * 6, 625 * 6};
                for (int i = 0; i < titles.length; i++) {
                    // 创建列
                    cell = row.createCell(i);
                    // 字体颜色
                    font.setColor(HSSFFont.COLOR_NORMAL);
                    cellStyle.setFont(font);
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                    //设置上边框线条类型
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    //设置右边框线条类型
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    //设置下边框线条类型
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    //设置左边框线条类型
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    //设置上边框线条颜色
                    cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                    //设置右边框线条颜色
                    cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                    //设置下边框线条颜色
                    cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                    //设置左边框线条颜色
                    cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                    cell.setCellStyle(cellStyle);
                    sheet.setColumnWidth(i, widths[i]);
                    // 设置列中写入内容为String类型
                    cell.setCellType(CellType.STRING);
                    // 写入列名
                    cell.setCellValue(titles[i]);
                }
                /* *************创建内容列*************** */
                font = workbook.createFont();
                //cellStyle = workbook.createCellStyle(); //继续沿用上面的样式
                String pic1Url = "";
                List<String> picList1 = new ArrayList<>();
                List<String> picList2 = new ArrayList<>();
                List<String> picList3 = new ArrayList<>();
                int rowSize = 10;
                if (rvList.size() + 1 > 10) {
                    rowSize = rvList.size() + 1;
                }
                for (int r = 1; r < 10; r++) {
                    row = sheet.createRow(r);// 产生一行
                    row.setHeight((short) 700);
                    String date = "";
                    String driverName = "";
                    String licensePlateNumber = "";
                    String clearingRoute = "";
                    String garbageRemovalRequirements = "";
                    String barrelage = "";
                    String weight = "";
                    String garbageBinCondition = "";
                    String treatmentStation = "";
                    if (rvList.size() > 0) {
                        if (rvList.size() >= r) {
                            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                            date = format.format(rvList.get(r - 1).getDate());
                            driverName = rvList.get(r - 1).getDriverName();
                            licensePlateNumber = rvList.get(r - 1).getLicensePlateNumber();
                            clearingRoute = rvList.get(r - 1).getClearingRoute();
                            garbageRemovalRequirements = rvList.get(r - 1).getGarbageRemovalRequirements();
                            barrelage = rvList.get(r - 1).getBarrelage() == null ? "" : rvList.get(r - 1).getBarrelage().toString();
                            weight = rvList.get(r - 1).getWeight() == null ? "" : rvList.get(r - 1).getWeight().toString();
                            garbageBinCondition = rvList.get(r - 1).getGarbageBinCondition();
                            treatmentStation = rvList.get(r - 1).getTreatmentStation();
                            pic1Url = rvList.get(r - 1).getPic1() != null ? ossService.selectUrlByIds(rvList.get(r - 1).getPic1()) : "";
                            String pic2Url = rvList.get(r - 1).getPic2() != null ? ossService.selectUrlByIds(rvList.get(r - 1).getPic2()) : "";
                            if(!pic1Url.equals("")) {
                                picList1.add(pic1Url);
                            }
                            //picList1.add(pic2Url);
                            String pic3Url = rvList.get(r - 1).getPic3() != null ? ossService.selectUrlByIds(rvList.get(r - 1).getPic3()) : "";
                            String pic4Url = rvList.get(r - 1).getPic4() != null ? ossService.selectUrlByIds(rvList.get(r - 1).getPic4()) : "";
                            if(!pic3Url.equals("")) {
                                picList2.add(pic3Url);
                            }
                            //picList2.add(pic4Url);
                            String pic5Url = rvList.get(r - 1).getPic5() != null ? ossService.selectUrlByIds(rvList.get(r - 1).getPic5()) : "";
                            String pic6Url = rvList.get(r - 1).getPic6() != null ? ossService.selectUrlByIds(rvList.get(r - 1).getPic6()) : "";
                            if(!pic5Url.equals("")) {
                                picList3.add(pic5Url);
                            }
                            // picList3.add(pic6Url);
                        }
                    }
                    for (int i = 0; i < 14; i++) {
                        // 创建列
                        cell = row.createCell(i);
                        // 字体颜色
                        font.setColor(HSSFFont.COLOR_NORMAL);
                        cellStyle.setFont(font);
                        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                        cell.setCellStyle(cellStyle);
                        // 设置列中写入内容为String类型
                        cell.setCellType(CellType.STRING);
                        // 写入列名
                        if (r == 1) {
                            List<StatisticsRecycleVo> srvList = rev.getBarrelageTodayList();
                            StatisticsRecycleVo srv = new StatisticsRecycleVo();
                            for (StatisticsRecycleVo statisticsRecycleVo : srvList) {
                                if (statisticsRecycleVo.getGarbageCategory().equals("1")) {
                                    srv = statisticsRecycleVo;
                                    break;
                                }
                            }
                            if (i == 0) {
                                cell.setCellValue("厨余垃圾清运");
                            } else if (i == 1) {
                                cell.setCellValue("今日桶数（L）：" + (srv.getBarrelage() == null ? 0 : srv.getBarrelage()));
                            } else if (i == 2) {
                                cell.setCellValue("本月累计桶数（L）：" + (srv.getThisMonthBarrelage() == null ? 0 : srv.getThisMonthBarrelage()));
                            } else if (i == 3) {
                                cell.setCellValue("");
                            } else if (i == 4) {
                                cell.setCellValue(date != "" ? r + "" : "");
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                cell.setCellValue(licensePlateNumber);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                cell.setCellValue(barrelage);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        } else if (r == 2) {
                            List<StatisticsRecycleVo> srvList = rev.getBarrelageTodayList();
                            StatisticsRecycleVo srv = new StatisticsRecycleVo();
                            for (StatisticsRecycleVo statisticsRecycleVo : srvList) {
                                if (statisticsRecycleVo.getGarbageCategory().equals("3")) {
                                    srv = statisticsRecycleVo;
                                    break;
                                }
                            }
                            if (i == 0) {
                                cell.setCellValue("生活垃圾清运");
                            } else if (i == 1) {
                                cell.setCellValue("今日桶数（L）：" + (srv.getBarrelage() == null ? 0 : srv.getBarrelage()));
                            } else if (i == 2) {
                                cell.setCellValue("本月累计桶数（L）：" + (srv.getThisMonthBarrelage() == null ? 0 : srv.getThisMonthBarrelage()));
                            } else if (i == 3) {
                                cell.setCellValue("");
                            } else if (i == 4) {
                                String title = "";
                                if (rvList.size() == 0) {
                                    title = "1.出车前检查图片";
                                }
                                cell.setCellValue(date != "" ? r + "" : title);
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                String title = "";
                                if (rvList.size() == 0) {
                                    title = "2.清运过程图片";
                                }
                                cell.setCellValue(licensePlateNumber != "" ? licensePlateNumber : title);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                String title = "";
                                if (rvList.size() == 0) {
                                    title = "3.车辆进站图片";
                                }
                                cell.setCellValue(barrelage != "" ? barrelage : title);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        } else if (r == 3) {
                            List<StatisticsRecycleVo> srvList = rev.getBarrelageTodayList();
                            StatisticsRecycleVo srv = new StatisticsRecycleVo();
                            for (StatisticsRecycleVo statisticsRecycleVo : srvList) {
                                if (statisticsRecycleVo.getGarbageCategory().equals("2")) {
                                    srv = statisticsRecycleVo;
                                    break;
                                }
                            }
                            if (i == 0) {
                                cell.setCellValue("废旧家具清运");
                            } else if (i == 1) {
                                cell.setCellValue("今日重量（KG）：" + (srv.getWeight() == null ? 0 : srv.getWeight()));
                            } else if (i == 2) {
                                cell.setCellValue("本月累计重量（KG）：" + (srv.getThisMonthWeight() == null ? 0 : srv.getThisMonthWeight()));
                            } else if (i == 3) {
                                cell.setCellValue("");
                            } else if (i == 4) {
                                String title = "";
                                if (rvList.size() == 1) {
                                    title = "1.出车前检查图片";
                                }
                                cell.setCellValue(date != "" ? r + "" : title);
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                String title = "";
                                if (rvList.size() == 1) {
                                    title = "2.清运过程图片";
                                }
                                cell.setCellValue(licensePlateNumber == "" ? title : licensePlateNumber);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                String title = "";
                                if (rvList.size() == 1) {
                                    title = "3.车辆进站图片";
                                }
                                cell.setCellValue(barrelage == "" ? title : barrelage);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        } else if (r == 4) {
                            if (i == 0) {
                                cell.setCellValue("清运合计");
                            } else if (i == 1) {
                                cell.setCellValue("今日桶数（L）：" + (rev.getTodayBarrelage() == null ? 0 : rev.getTodayBarrelage()));
                            } else if (i == 2) {
                                cell.setCellValue("本月累计桶数（L）：" + (rev.getThisMonthBarrelage() == null ? 0 : rev.getThisMonthBarrelage()));
                            } else if (i == 3) {
                                cell.setCellValue("");
                            } else if (i == 4) {
                                String title = "";
                                if (rvList.size() == 2) {
                                    title = "1.出车前检查图片";
                                }
                                cell.setCellValue(date != "" ? r + "" : title);
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                String title = "";
                                if (rvList.size() == 2) {
                                    title = "2.清运过程图片";
                                }
                                cell.setCellValue(licensePlateNumber == "" ? title : licensePlateNumber);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                String title = "";
                                if (rvList.size() == 2) {
                                    title = "3.车辆进站图片";
                                }
                                cell.setCellValue(barrelage == "" ? title : barrelage);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        } else if (r == 5) {
                            if (i == 0) {
                                cell.setCellValue("");
                            } else if (i == 1) {
                                cell.setCellValue("今日重量（KG）：0");
                            } else if (i == 2) {
                                cell.setCellValue("本月累计重量（KG）：0");
                            } else if (i == 3) {
                                cell.setCellValue("");
                            } else if (i == 4) {
                                String title = "";
                                if (rvList.size() == 3) {
                                    title = "1.出车前检查图片";
                                }
                                cell.setCellValue(date != "" ? r + "" : title);
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                String title = "";
                                if (rvList.size() == 3) {
                                    title = "2.清运过程图片";
                                }
                                cell.setCellValue(licensePlateNumber == "" ? title : licensePlateNumber);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                String title = "";
                                if (rvList.size() == 3) {
                                    title = "3.车辆进站图片";
                                }
                                cell.setCellValue(barrelage == "" ? title : barrelage);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        } else if (r == 6) {
                            if (i == 0) {
                                cell.setCellValue("");
                            } else if (i == 1) {
                                cell.setCellValue("");
                            } else if (i == 2) {
                                cell.setCellValue("");
                            } else if (i == 3) {
                                cell.setCellValue("");
                            } else if (i == 4) {
                                String title = "";
                                if (rvList.size() == 4) {
                                    title = "1.出车前检查图片";
                                }
                                cell.setCellValue(date != "" ? r + "" : title);
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                String title = "";
                                if (rvList.size() == 4) {
                                    title = "2.清运过程图片";
                                }
                                cell.setCellValue(licensePlateNumber == "" ? title : licensePlateNumber);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                String title = "";
                                if (rvList.size() == 4) {
                                    title = "3.车辆进站图片";
                                }
                                cell.setCellValue(barrelage == "" ? title : barrelage);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        } else if (r == 7) {
                            List<StatisticsRecycleVo> srvList = rev.getCarNumberTodayList();
                            StatisticsRecycleVo srv = new StatisticsRecycleVo();
                            for (StatisticsRecycleVo statisticsRecycleVo : srvList) {
                                if (statisticsRecycleVo.getGarbageCategory().equals("1")) {
                                    srv = statisticsRecycleVo;
                                    break;
                                }
                            }
                            if (i == 0) {
                                cell.setCellValue("厨余垃圾车辆");
                            } else if (i == 1) {
                                cell.setCellValue("今日投入：" + (srv.getCarNumber() == null ? 0 : srv.getCarNumber()) + "辆");
                            } else if (i == 2) {
                                cell.setCellValue("本月累计投入：" + (srv.getThisMonthCarNumber() == null ? 0 : srv.getThisMonthCarNumber()) + "辆");
                            } else if (i == 3) {
                                cell.setCellValue("");
                            } else if (i == 4) {
                                String title = "";
                                if (rvList.size() == 5) {
                                    title = "1.出车前检查图片";
                                }
                                cell.setCellValue(date != "" ? r + "" : title);
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                String title = "";
                                if (rvList.size() == 5) {
                                    title = "2.清运过程图片";
                                }
                                cell.setCellValue(licensePlateNumber == "" ? title : licensePlateNumber);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                String title = "";
                                if (rvList.size() == 5) {
                                    title = "3.车辆进站图片";
                                }
                                cell.setCellValue(barrelage == "" ? title : barrelage);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        } else if (r == 8) {
                            List<StatisticsRecycleVo> srvList = rev.getCarNumberTodayList();
                            StatisticsRecycleVo srv = new StatisticsRecycleVo();
                            for (StatisticsRecycleVo statisticsRecycleVo : srvList) {
                                if (statisticsRecycleVo.getGarbageCategory().equals("3")) {
                                    srv = statisticsRecycleVo;
                                    break;
                                }
                            }
                            if (i == 0) {
                                cell.setCellValue("生活垃圾车辆");
                            } else if (i == 1) {
                                cell.setCellValue("今日投入：" + (srv.getCarNumber() == null ? 0 : srv.getCarNumber()) + "辆");
                            } else if (i == 2) {
                                cell.setCellValue("本月累计投入：" + (srv.getThisMonthCarNumber() == null ? 0 : srv.getThisMonthCarNumber()) + "辆");
                            } else if (i == 3) {
                                cell.setCellValue("");
                            } else if (i == 4) {
                                String title = "";
                                if (rvList.size() == 6) {
                                    title = "1.出车前检查图片";
                                }
                                cell.setCellValue(date != "" ? r + "" : title);
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                String title = "";
                                if (rvList.size() == 6) {
                                    title = "2.清运过程图片";
                                }
                                cell.setCellValue(licensePlateNumber == "" ? title : licensePlateNumber);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                String title = "";
                                if (rvList.size() == 6) {
                                    title = "3.车辆进站图片";
                                }
                                cell.setCellValue(barrelage == "" ? title : barrelage);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        } else if (r == 9) {
                            List<StatisticsRecycleVo> srvList = rev.getCarNumberTodayList();
                            StatisticsRecycleVo srv = new StatisticsRecycleVo();
                            for (StatisticsRecycleVo statisticsRecycleVo : srvList) {
                                if (statisticsRecycleVo.getGarbageCategory().equals("3")) {
                                    srv = statisticsRecycleVo;
                                    break;
                                }
                            }
                            if (i == 0) {
                                cell.setCellValue("废旧家具车辆");
                            } else if (i == 1) {
                                cell.setCellValue("今日投入：" + (srv.getCarNumber() == null ? 0 : srv.getCarNumber()) + "辆");
                            } else if (i == 2) {
                                cell.setCellValue("本月累计投入：" + (srv.getThisMonthCarNumber() == null ? 0 : srv.getThisMonthCarNumber()) + "辆");
                            } else if (i == 3) {
                                cell.setCellValue("");
                            } else if (i == 4) {
                                String title = "";
                                if (rvList.size() == 7) {
                                    title = "1.出车前检查图片";
                                }
                                cell.setCellValue(date != "" ? r + "" : title);
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                String title = "";
                                if (rvList.size() == 7) {
                                    title = "2.清运过程图片";
                                }
                                cell.setCellValue(licensePlateNumber == "" ? title : licensePlateNumber);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                String title = "";
                                if (rvList.size() == 7) {
                                    title = "3.车辆进站图片";
                                }
                                cell.setCellValue(barrelage == "" ? title : barrelage);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        } else if (r == 10) {
                            if (i == 0) {
                                cell.setCellValue("投入合计");
                            } else if (i == 1) {
                                cell.setCellValue("今日投入：" + (rev.getTodayCarNumber() == null ? 0 : rev.getTodayCarNumber()) + "辆");
                            } else if (i == 2) {
                                cell.setCellValue("本月累计投入：" + (rev.getThisMonthCarNumber() == null ? 0 : rev.getThisMonthCarNumber()) + "辆");
                            } else if (i == 3) {
                                cell.setCellValue("");
                            } else if (i == 4) {
                                String title = "";
                                if (rvList.size() == 8) {
                                    title = "1.出车前检查图片";
                                }
                                cell.setCellValue(date != "" ? r + "" : title);
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                String title = "";
                                if (rvList.size() == 8) {
                                    title = "2.清运过程图片";
                                }
                                cell.setCellValue(licensePlateNumber == "" ? title : licensePlateNumber);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                String title = "";
                                if (rvList.size() == 8) {
                                    title = "3.车辆进站图片";
                                }
                                cell.setCellValue(barrelage == "" ? title : barrelage);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        } else {
                            if (i == 4) {
                                String title = "";
                                if (rvList.size() + 2 == r) {
                                    title = "1.出车前检查图片";
                                }
                                cell.setCellValue(title);
                            } else if (i == 5) {
                                cell.setCellValue(date);
                            } else if (i == 6) {
                                cell.setCellValue(driverName);
                            } else if (i == 7) {
                                String title = "";
                                if (rvList.size() + 2 == r) {
                                    title = "2.清运过程图片";
                                }
                                cell.setCellValue(licensePlateNumber == "" ? title : licensePlateNumber);
                            } else if (i == 8) {
                                cell.setCellValue(clearingRoute);
                            } else if (i == 9) {
                                cell.setCellValue(garbageRemovalRequirements);
                            } else if (i == 10) {
                                String title = "";
                                if (rvList.size() + 2 == r) {
                                    title = "3.车辆进站图片";
                                }
                                cell.setCellValue(barrelage == "" ? title : barrelage);
                            } else if (i == 11) {
                                cell.setCellValue(weight);
                            } else if (i == 12) {
                                cell.setCellValue(garbageBinCondition);
                            } else if (i == 13) {
                                cell.setCellValue(treatmentStation);
                            }
                        }
                    }
                }

                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(4, 5, 0, 0));
                //合并单元格
                if (rvList.size() > 0) {
                    sheet.addMergedRegion(new CellRangeAddress(rvList.size() + 2, rvList.size() + 2, 4, 5));
                    sheet.addMergedRegion(new CellRangeAddress(rvList.size() + 2, rvList.size() + 2, 7, 8));
                    sheet.addMergedRegion(new CellRangeAddress(rvList.size() + 2, rvList.size() + 2, 10, 11));
                }

                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                int rowNum = rvList.size() + 3;
                for (int i = 0; i < picList1.size(); i++) {
                    URL url = new URL(picList1.get(i));
                    HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                    conn.setRequestMethod("GET");
                    InputStream inStream = conn.getInputStream();
                    byte[] byteData = readInputStream(inStream);
                    // 图片一导出到单元格
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 4, rowNum, (short) 4, rowNum);
                    //插入图片
                    patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));

                    if (picList1.size() >= i + 2) {
                        url = new URL(picList1.get(i + 1));
                        conn = (HttpURLConnection) url.openConnection();
                        conn.setRequestMethod("GET");
                        inStream = conn.getInputStream();
                        byteData = readInputStream(inStream);
                        // 图片一导出到单元格
                        anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 5, rowNum, (short) 5, rowNum);
                        //插入图片
                        patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                    i++;
                    rowNum++;
                }

                rowNum = rvList.size() + 3;
                for (int i = 0; i < picList2.size(); i++) {
                    URL url = new URL(picList2.get(i));
                    HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                    conn.setRequestMethod("GET");
                    InputStream inStream = conn.getInputStream();
                    byte[] byteData = readInputStream(inStream);

                    // 图片一导出到单元格
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 7, rowNum, (short) 7, rowNum);
                    //插入图片
                    patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));

                    if (picList2.size() >= i + 2) {
                        url = new URL(picList2.get(i + 1));
                        conn = (HttpURLConnection) url.openConnection();
                        conn.setRequestMethod("GET");
                        inStream = conn.getInputStream();
                        byteData = readInputStream(inStream);

                        // 图片一导出到单元格
                        anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 8, rowNum, (short) 8, rowNum);
                        //插入图片
                        patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                    i++;
                    rowNum++;
                }

                rowNum = rvList.size() + 3;
                for (int i = 0; i < picList3.size(); i++) {
                    URL url = new URL(picList3.get(i));
                    HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                    conn.setRequestMethod("GET");
                    InputStream inStream = conn.getInputStream();
                    byte[] byteData = readInputStream(inStream);

                    // 图片一导出到单元格
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 10, rowNum, (short) 10, rowNum);
                    //插入图片
                    patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));

                    if (picList3.size() >= i + 2) {
                        url = new URL(picList3.get(i + 1));
                        conn = (HttpURLConnection) url.openConnection();
                        conn.setRequestMethod("GET");
                        inStream = conn.getInputStream();
                        byteData = readInputStream(inStream);

                        // 图片一导出到单元格
                        anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 11, rowNum, (short) 11, rowNum);
                        //插入图片
                        patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                    i++;
                    rowNum++;
                }
            }

            output.flush();
            workbook.write(output);
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    private static byte[] readInputStream(InputStream inStream) throws Exception {
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        //创建一个Buffer字符串
        byte[] buffer = new byte[1024];
        //每次读取的字符串长度，如果为-1，代表全部读取完毕
        int len = 0;
        //使用一个输入流从buffer里把数据读取出来
        while ((len = inStream.read(buffer)) != -1) {
            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        //关闭输入流
        inStream.close();
        //把outStream里的数据写入内存
        return outStream.toByteArray();

    }
}
